﻿Imports System.Data
Imports System.Data.OleDb
Imports DTO
Public Class NhanVienDao

    Dim provider As New DataProvider

    Public Function LayNguoiDangNhap(ByVal tenDangNhap As String, ByVal matKhau As String) As Integer
        Dim sql As String = "SELECT count(*) FROM NhanVien WHERE TenDangNhap = '" & tenDangNhap & "' AND MatKhau = '" & matKhau & "'"
        Dim row As Integer = provider.ThucThiCauTruyVan(sql).Rows.Count
        Try
            If row > 0 Then
                sql = "SELECT LoaiNhanVien  FROM NhanVien Where TenDangNhap = '" & tenDangNhap & "' and MatKhau = '" & matKhau & "'"
                Dim loaiND As Integer = provider.ThucThiCauTruyVan(sql).Rows(0).Item(0)
                If loaiND = 1 Then
                    Return 1
                Else
                    Return 0
                End If
            End If
        Catch ex As Exception
            Return -1
        End Try
    End Function

    Public Function LayNguoiDung(ByVal tenDangNhap As String, ByVal matKhau As String) As DataTable
        Dim sql As String = "SELECT MaNhanVien, LoaiNhanVien FROM NhanVien WHERE TenDangNhap = '" & tenDangNhap & "' AND MatKhau = '" & matKhau & "'"
        Return provider.ThucThiCauTruyVan(sql)
    End Function

    Public Function LayNguoiDungTheoMa(ByVal maNhanVien As Long) As DataTable
        Dim sql As String = "SELECT * FROM NhanVien WHERE MaNhanVien = " & maNhanVien
        Return provider.ThucThiCauTruyVan(sql)
    End Function


    Public Function LayQuyen() As DataTable
        Dim sql As String = "SELECT pc.MaNhanVien,nv.TenDangNhap,pc.MaQuyen FROM NhanVien nv,PhanQuyen pc"
        Return provider.ThucThiCauTruyVan(sql)
    End Function
    Public Function LayNguoiDung() As DataTable
        Dim sql As String = "SELECT * FROM Quyen"
        Return provider.ThucThiCauTruyVan(sql)
    End Function

    Public Function LayDanhSachNguoiDung() As DataTable
        Dim sql As String = "SELECT MaNhanVien,MatKhau,TenDangNhap,HoTen,NgaySinh,IIF(Phai=1,'Nữ','Nam') AS Phai,DiaChi,DienThoai,loaiNhanVien FROM NhanVien "
        Return provider.ThucThiCauTruyVan(sql)
    End Function

    Public Function ThemNhanVien(ByVal nhanVien As NhanVienDto) As Integer
        Dim sql As String = "insert into NhanVien (TenDangNhap,MatKhau,HoTen,NgaySinh,Phai,DiaChi,DienThoai,LoaiNhanVien) values (?,?,?,?,?,?,?,?)"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@TenDangNhap", nhanVien.TenDangNhap))
        dsParameter.Add(New OleDbParameter("@MatKhau", nhanVien.MatKhau))
        dsParameter.Add(New OleDbParameter("@HoTen", nhanVien.HoTen))
        dsParameter.Add(New OleDbParameter("@NgaySinh", nhanVien.NgaySinh))
        dsParameter.Add(New OleDbParameter("@Phai", nhanVien.GioiTinh))
        dsParameter.Add(New OleDbParameter("@DiaChi", nhanVien.DiaChi))
        dsParameter.Add(New OleDbParameter("@DienThoai", nhanVien.DienThoai))
        dsParameter.Add(New OleDbParameter("@LoaiNhanVien", nhanVien.LoaiNhanVien))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function
    Public Function CapNhatNhanVien(ByVal nhanVien As NhanVienDto) As Integer
        Dim sql As String = "UPDATE NhanVien SET TenDangNhap = ?, MatKhau = ?, HoTen = ?, NgaySinh = ?, Phai = ?, DiaChi = ?,DienThoai = ?, LoaiNhanVien = ? WHERE MaNhanVien = ?"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@TenDangNhap", nhanVien.TenDangNhap))
        dsParameter.Add(New OleDbParameter("@MatKhau", nhanVien.MatKhau))
        dsParameter.Add(New OleDbParameter("@HoTen", nhanVien.HoTen))
        dsParameter.Add(New OleDbParameter("@NgaySinh", nhanVien.NgaySinh))
        dsParameter.Add(New OleDbParameter("@Phai", nhanVien.GioiTinh))
        dsParameter.Add(New OleDbParameter("@DiaChi", nhanVien.DiaChi))
        dsParameter.Add(New OleDbParameter("@DienThoai", nhanVien.DienThoai))
        dsParameter.Add(New OleDbParameter("@LoaiNhanVien", nhanVien.LoaiNhanVien))
        dsParameter.Add(New OleDbParameter("@MaNhanVien", nhanVien.MaNhanVien))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function

    Public Function TimKiemNhanVien(ByVal nhanVien As NhanVienDto) As DataTable
        Dim sql As String = "select MaNhanVien,MatKhau,TenDangNhap,HoTen,NgaySinh,IIF(Phai=1,'Nữ','Nam') AS Phai,DiaChi,DienThoai,loaiNhanVien from NHANVIEN where 1"
        If nhanVien.TenDangNhap <> "" Then
            sql += " and TenDangNhap LIKE '%" & nhanVien.TenDangNhap & "%'"
        End If
        If nhanVien.HoTen <> "" Then
            sql += " and HoTen LIKE '%" & nhanVien.HoTen & "%'"
        End If
        If nhanVien.DienThoai <> "" Then
            sql += " and DienThoai LIKE '%" & nhanVien.DienThoai & "%'"
        End If
        If nhanVien.GioiTinh = 1 Then
            sql += " and Phai = " & nhanVien.GioiTinh & ""
        End If
        If nhanVien.GioiTinh = 0 Then
            sql += " and Phai = " & nhanVien.GioiTinh & ""
        End If
        If nhanVien.LoaiNhanVien = 1 Then
            sql += " and LoaiNhanVien = " & nhanVien.LoaiNhanVien & ""
        End If
        If nhanVien.LoaiNhanVien = 0 Then
            sql += " and LoaiNhanVien = " & nhanVien.LoaiNhanVien & ""
        End If
        Return provider.ThucThiCauTruyVan(sql)
    End Function

    Public Function XoaNhanVien(ByVal nhanVien As NhanVienDto) As String
        Dim str As String = ""
        Try
            Dim con As OleDbConnection = provider.Connect()
            Dim sql As String = "delete from NHANVIEN where MaNhanVien = " & nhanVien.MaNhanVien.ToString()

            Dim cmd As OleDbCommand = New OleDbCommand(sql, con)
            cmd.Parameters.Add("@MaNhanVien", OleDbType.Integer)
            cmd.Parameters("@MaNhanVien").Value = nhanVien.MaNhanVien

            cmd.ExecuteNonQuery()
            con.Close()
        Catch ex As Exception
            str = ex.Message
        End Try
        Return str
    End Function
End Class

